﻿#region Using namespaces

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;

using BOL.Models.Gene;
using BOL.Models.Genome;

#endregion

namespace BOL.Apps.GeneNetworkBrowser.Databases
{
    public static class Mapping
    {
        private static SQLiteConnection connGR = new SQLiteConnection("Properties.Settings.Default.GeneRelations_Human");
        private static SQLiteConnection connAH = new SQLiteConnection("Properties.Settings.Default.Annotations_Human");
        private static SQLiteConnection connMA = new SQLiteConnection("Properties.Settings.Default.Microarray_Affymetrix");

        public static string IDToSymbol(int id)
        {
            string symbol = String.Empty;

            connAH.Open();
            using (SQLiteCommand selectComm = connAH.CreateCommand())
            {
                selectComm.CommandText = String.Format("SELECT Symbol FROM GeneInfo WHERE ID = {0}", id);
                var scalar = selectComm.ExecuteScalar();
                if (scalar == null)
                    symbol = scalar.ToString();
            }
            connAH.Close();

            return symbol;
        }

        public static IEnumerable<string> IDToSymbol(IEnumerable<int> ids)
        {
            var symbols = new HashSet<string>();

            connAH.Open();
            using (SQLiteCommand selectComm = connAH.CreateCommand())
            {
                selectComm.CommandText = String.Format("SELECT Symbol FROM GeneInfo WHERE ID IN ({0})", String.Join(",", ids));
                var reader = selectComm.ExecuteReader();
                while (reader.Read())
                    symbols.Add(reader.GetString(0));
            }
            connAH.Close();

            return symbols;
        }

        public static string SymbolToID(string symbol)
        {
            int id = 0;

            connAH.Open();
            using (SQLiteCommand selectComm = connAH.CreateCommand())
            {
                selectComm.CommandText = String.Format("SELECT ID FROM Symbol2ID WHERE Symbol = {0}", id);
                var scalar = selectComm.ExecuteScalar();
                if (scalar == null)
                    id = Convert.ToInt32(scalar);
            }
            connAH.Close();

            return symbol;
        }

        public static IEnumerable<int> SymbolToID(IEnumerable<string> symbols)
        {
            var ids = new HashSet<int>();

            connAH.Open();
            using (SQLiteCommand selectComm = connAH.CreateCommand())
            {
                selectComm.CommandText = String.Format("SELECT ID FROM Symbol2ID WHERE Symbol IN ({0})", String.Join(",", symbols));
                var reader = selectComm.ExecuteReader();
                while (reader.Read())
                    ids.Add(reader.GetInt32(0));
            }
            connAH.Close();

            return ids;
        }
    }
}
